package dbms;

import java.sql.SQLException;
import java.sql.Statement;

public class DBTables {

	public static String CONFIG = "config";
	public static String UTENTE = "utente";
	public static String PROGRAM = "program";
	public static String PROG_UTENTE = "program_utente";
	public static String FILE = "file";
	public static String RELEASE = "release";
	
	public static String CONFIG_TAB =		"CREATE TABLE IF NOT EXISTS " + DBTables.CONFIG + " (" +
											"`id` int(11) NOT NULL," +
											"`value` varchar(100) NOT NULL," +
											"PRIMARY KEY (`id`))";

	public static String UTENTE_TAB =		"CREATE TABLE IF NOT EXISTS " + DBTables.UTENTE + " (" +
											"`userid` varchar(30) NOT NULL," +
											"`password` varchar(32) NOT NULL," +
											"PRIMARY KEY (`userid`))";

	public static String PROGRAM_TAB = 		"CREATE TABLE IF NOT EXISTS " + DBTables.PROGRAM + " (" +
											"`name` varchar(30) NOT NULL," +
											"`extenzion` varchar(30) NOT NULL," +
			  								"`new_file` varchar(3) NOT NULL DEFAULT '1'," +
											"PRIMARY KEY (`name`))"; 
	
	public static String PROG_UTENTE_TAB = 	"CREATE TABLE IF NOT EXISTS " + DBTables.PROG_UTENTE + " (" +
			  								"`id` int(11) NOT NULL AUTO_INCREMENT," +
			  								"`utente` varchar(30) NOT NULL," +
			  								"`program` varchar(30) NOT NULL," +
			  								"PRIMARY KEY (`id`)," +
			  								"FOREIGN KEY (`utente`) REFERENCES utente(`userid`)" +
			  								"ON DELETE CASCADE " +
			  								"ON UPDATE CASCADE," +
			  								"FOREIGN KEY (`program`) REFERENCES program(`name`)" +
			  								"ON DELETE CASCADE " +
			  								"ON UPDATE CASCADE)";  								
	
	public static String FILE_TAB = 		"CREATE TABLE IF NOT EXISTS " + DBTables.FILE + " (" +
											"`id` int(11) NOT NULL AUTO_INCREMENT," +
											"`name` VARCHAR( 15 ) NOT NULL," +
											"`program` varchar(30) NOT NULL," +
											"`last_edit_by` varchar(30) NOT NULL," +
											"`last_edit_when` TIMESTAMP NOT NULL," +
	  										"`release_number` int(11) NOT NULL," +											
			  								"PRIMARY KEY (`id`)," +
			  								"FOREIGN KEY (`program`) REFERENCES program(`name`)" +
			  								"ON DELETE CASCADE " +
			  								"ON UPDATE CASCADE)";
	
	public static String RELEASE_TAB = 		"CREATE TABLE IF NOT EXISTS " + DBTables.RELEASE + " (" +
	  										"`id` int(11) NOT NULL AUTO_INCREMENT," +
	  										"`file` int(11) NOT NULL," +
	  										"`added_by` varchar(30) NOT NULL," +
	  										"`added_when` timestamp NOT NULL," +
	  										"`number` int(11) NOT NULL," +
	  										"`note` TEXT NULL," +
	  										"PRIMARY KEY (`id`)," +
			  								"FOREIGN KEY (`file`) REFERENCES file(`id`) " +
			  								"ON DELETE CASCADE " +
			  								"ON UPDATE CASCADE," +	
											"FOREIGN KEY (`added_by`) REFERENCES utente(`userid`) " +
											"ON DELETE CASCADE " +
											"ON UPDATE CASCADE)";
	
	public static boolean createDB()
	{
		try {
		
			DBConnection.getIstance().setAutoCommit(false);
			Statement query = DBConnection.getIstance().createStatement();
			
			query.execute(DBTables.CONFIG_TAB);
			query.execute(DBTables.UTENTE_TAB);
			query.execute(DBTables.PROGRAM_TAB);
			query.execute(DBTables.PROG_UTENTE_TAB);
			query.execute(DBTables.FILE_TAB);
			query.execute(DBTables.RELEASE_TAB);
			
			DBConnection.getIstance().commit();
			DBConnection.getIstance().setAutoCommit(true);	
		
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			return false;
		}
	}
	/*
	public static void main(String[] args) {		
		try {
			Statement query = DBConnection.getIstance().createStatement();
			//query.execute(Tables.UTENTE);
			//query.execute("INSERT INTO utente VALUES ('ciao', 'ciao')");
			//query.execute("INSERT INTO utente VALUES ('io', 'io')");
			
			PreparedStatement query2 = DBConnection.getIstance().prepareStatement("SELECT * FROM utente");
			ResultSet res = query2.executeQuery();
			
			String utente, pass;
			while(res.next())
			{
				utente = res.getString(1);
				pass = res.getString(2);
				
				System.out.println(utente + " " + pass);
				
			}		
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}*/
	
}